﻿using Sc.Domain;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Sc.Persistence
{
    public class OpinionsDAL
    {
        // <summary>
        /// 增加一条数据
        /// </summary>
        public bool Add(Opinions model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into Opinions(");
            strSql.Append("Source,UserId,Context,CreateTime)");
            strSql.Append(" values (");
            strSql.Append("@Source,@UserId,@Context,@CreateTime)");
            SqlParameter[] parameters = {
					new SqlParameter("@Source", SqlDbType.NVarChar,50),
					new SqlParameter("@UserId", SqlDbType.Int,4),
					new SqlParameter("@Context", SqlDbType.NVarChar,200),
					new SqlParameter("@CreateTime", SqlDbType.DateTime)};
            parameters[0].Value = model.Source;
            parameters[1].Value = model.UserId;
            parameters[2].Value = model.Context;
            parameters[3].Value = model.CreateTime;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 根据
        /// </summary>
        /// <param name="source"></param>
        /// <returns></returns>
        public List<Opinions> GetList(string source)
        {
            List<Opinions> result = new List<Opinions>();
            string sql = "SELECT * FROM VW_Opinions WHERE Source=@Source order by CreateTime";
            SqlParameter[] param = new SqlParameter[]{
                new SqlParameter("@Source",SqlDbType.VarChar)
            };
            param[0].Value = source;
            using (SqlDataReader sdr = DbHelperSQL.ExecuteReader(sql, param))
            {
                while (sdr.Read())
                {
                    Opinions obj = new Opinions();
                    obj = BuildObjectFromIDataReader(sdr);
                    result.Add(obj);
                }
                sdr.Close();
            }
            return result;
        }
        /// <summary>
        /// 从IDataReader中读取数据，组装成实体
        /// </summary>
        /// <param name="dr">数据读取接口</param>
        /// <returns>Opinions实体</returns>
        /// <remarks>
        /// </remarks>
        private Opinions BuildObjectFromIDataReader(IDataReader dr)
        {
            try
            {
                Opinions obj = new Opinions();
                obj.Id = (dr["Id"] == System.DBNull.Value ? 0 : dr.GetInt32(dr.GetOrdinal("Id")));
                obj.Source = (dr["Source"] == System.DBNull.Value ? String.Empty : dr.GetString(dr.GetOrdinal("Source")));
                obj.UserId = (dr["UserId"] == System.DBNull.Value ? 0 : dr.GetInt32(dr.GetOrdinal("UserId")));
                obj.Context = (dr["Context"] == System.DBNull.Value ? String.Empty : dr.GetString(dr.GetOrdinal("Context")));
                obj.CreateTime = (dr["CreateTime"] == System.DBNull.Value ? new DateTime(1900, 1, 1) : dr.GetDateTime(dr.GetOrdinal("CreateTime")));
                obj.UserName = (dr["Name"] == System.DBNull.Value ? String.Empty : dr.GetString(dr.GetOrdinal("Name")));
                return obj;
            }
            catch (Exception ex)
            {
                throw new Exception("从DataReader中读取数据失败，可能与相关的字段不存在有关！请检查！||" + ex.Message + "||" + ex.Source);
            }
        }	
    }
}
